
clear all 

//define folder global here

gl Inputs "$folder/1_Inputs"
gl Intermediate "$folder/2_Intermediate"
gl Outputs "$folder/3_Outputs"

********************************************************************************
* CLEAN ACS DATA 
********************************************************************************
use "${Inputs}/ACS_sumstats.dta", clear 

	gen 	data = "acs2"

	gen 	variable = "age" if regexm(name, "Age at Filing")
	replace variable = "fem" if regexm(name, "Female")
	replace variable = "bla" if regexm(name, "Black")
	replace variable = "hisp" if regexm(name, "Hispanic")
	replace variable = "nonh" if regexm(name, "Not-Hispanic")
	replace variable = "earn" if name=="Earnings"
	replace variable = "earnxx" if name=="Earnings, Conditional on Employed"
	replace variable = "prop" if name=="Employed"
	replace variable = "povr" if regexm(name, "Poverty Rate")
	replace variable = "rent" if regexm(name, "Median Rent")
	
	gen 	geo = "ny" if location=="nyc"
	replace geo = "chi" if location=="cc"
	
	drop name evicted location 

	preserve // keep N for one of the obs
		keep if variable=="age"
		replace variable = "obs"
		drop sd mean
		rename N mean
		
		tempfile acs_obs
		save `acs_obs'
	restore 
	
	drop N 
	append using `acs_obs'
	
	rename (sd mean) (numse nummean)
	reshape long num , i(variable data geo) j(type) string
	
	tempfile new_acs_numbers
	save `new_acs_numbers'
	 
********************************************************************************
* CLEAN COOK DATA
********************************************************************************
import delimited "${Inputs}/Cook_lehd_sumstats.csv", clear 

	rename variable name

	gen 	variable = "age" if regexm(name, "ageatcase")
	replace variable = "fem" if regexm(name, "fem")
	replace variable = "bla" if regexm(name, "black")
	replace variable = "nonh" if regexm(name, "white")
	replace variable = "earn" if regexm(name, "earn_b8to1")
	replace variable = "prop" if regexm(name, "anyearn_allst_b8to1")
	replace variable = "povr" if regexm(name, "pov_rate")
	replace variable = "rent" if regexm(name, "med_rent")
	replace variable = "dam" if regexm(name, "ad_damnum")
	replace variable = "prio" if regexm(name, "noprior")
	replace variable = "att" if regexm(name, "no_attorney")
	replace variable = "obs" if regexm(name, "Obs")
	replace variable = "hisp" if regexm(name, "hispanic")
	replace variable = "join" if regexm(name, "joint")
	
	drop if mi(variable)
	drop name 
	
	reshape long mean se , i(variable) j(data) string
	
	replace data = "acs" if regexm(data, "acs")
	replace data = "not" if regexm(data, "not_evicted")
	replace data = "evi" if regexm(data, "evicted")

	rename (mean se) (nummean numse)
	reshape long num , i(variable data) j(type) string
	drop if mi(num)
	
	gen geo = "chi"
	compress 
	
	drop if data=="acs"
	
	tempfile chi_clean
	save `chi_clean'
	
********************************************************************************
* CLEAN NY DATA 
********************************************************************************
use "${Inputs}/NY_main_sumstats.dta", clear

	gen 	data = "evi" if regexm(evicted, "Evicted")
	replace data = "not" if regexm(evicted, "Not Evicted")
	
	gen 	variable = "age" if regexm(name, "Age at Filing")
	replace variable = "fem" if regexm(name, "Female")
	replace variable = "bla" if regexm(name, "Black")
	replace variable = "hisp" if regexm(name, "Hispanic")
	replace variable = "nonh" if regexm(name, "Not-Hispanic")
	replace variable = "earn" if name=="Earnings Q -1,-8"
	replace variable = "prop" if name=="Employment Q -1,-8"
	replace variable = "povr" if regexm(name, "Tract Poverty Rate")
	replace variable = "rent" if regexm(name, "Tract Median Rent")
	replace variable = "dam" if regexm(name, "Rent Amount Owed")
	replace variable = "prio" if regexm(name, "No Previous Case")
	replace variable = "att" if regexm(name, "No Attorney")
	
	drop if mi(variable) | mi(data)
	drop evicted name
	
	preserve // keep N for case-specific variables, like attorney
		keep if variable=="att"
		replace variable = "obs"
		drop sd mean
		rename N mean
		
		tempfile ny_obs
		save `ny_obs'
	restore 
	
	drop N 
	append using `ny_obs'
	
	rename (sd mean) (numse nummean)
	reshape long num , i(variable data) j(type) string
	
	drop if data=="acs"
	
	gen geo = "ny"
	compress 
		

********************************************************************************
* CLEAN + MAKE STRINGS
********************************************************************************
	append using `chi_clean'
	append using `new_acs_numbers'
	
	order geo data variable type, first
	
	assert data!="acs"
	replace data="acs" if data=="acs2"
	
	gen num2 = string(num, "%10.2fc")
	replace num2 = string(num, "%10.0fc") if variable=="earn" | variable == "rent" | variable == "obs"
	drop num 
	rename num2 num	
	
	replace type = "m" if type=="mean"
	
	replace num = "(" + num + ")" if type=="se"
	
	replace num = subinstr(num, ".00", "", .) if variable=="obs"
	drop if num=="(.)"
	
	
********************************************************************************
* MAKE TABLE
********************************************************************************
levelsof geo, local(locs)
levelsof data, local(dats)
levelsof variable, local(vars)
levelsof type, local(typs)

foreach loc in `locs'{
	if "`loc'" == "`loc'" local id1 = "`loc'"
	
	foreach dat in `dats'{
		if "`dat'" == "`dat'" local id2 = "`dat'"
		
		foreach var in `vars' {
			if "`var'" == "`var'" local id3 = "`var'"
			
			foreach typ in `typs' {
				if "`typ'" == "`typ'" local id4 = "`typ'"
				
				levelsof num if geo=="`loc'" & ///
								data=="`dat'" & ///
								variable=="`var'" & ///
								type=="`typ'", ///
								clean local(`id1'_`id2'_`id3'_`id4')
				
				di "`id1'_`id2'_`id3'_`id4'"
				
			}
		}
	}
}
 
 		
gl x " "	
		
texdoc init "${Outputs}/Table_1.tex", replace force 
tex {\footnotesize \begin{tabular}{l c c c c c c c} \hline \toprule 
tex & \multicolumn{3}{c}{\textbf{Cook County}} & & \multicolumn{3}{c}{\textbf{New York}} \\
tex \cmidrule{2-4} \cmidrule{6-8} 

tex & & & Renters & & & & Renters \\ 
tex & \multirow{ 2}{*}{Evicted} & Not & from same & & \multirow{ 2}{*}{Evicted} & Not & from same \\
tex & & Evicted & neighbor- & & & Evicted & neighbor- \\
tex & & & hoods & & & & hoods \\

tex	& (1) & (2) & (3) & & (4) & (5) & (6) \\
tex \cmidrule{2-4} \cmidrule{6-8}  

tex $x \textbf{\underline{Individual characteristics:}} & & & & & & & \\

tex $x ~~Age & `chi_evi_age_m' & `chi_not_age_m' & `chi_acs_age_m' & & `ny_evi_age_m' & `ny_not_age_m' & `ny_acs_age_m' \\ 
tex & `chi_evi_age_se' & `chi_not_age_se' & `chi_acs_age_se' & & `ny_evi_age_se' & `ny_not_age_se' & `ny_acs_age_se' \\ 
 
tex $x ~~Female & `chi_evi_fem_m' & `chi_not_fem_m' & `chi_acs_fem_m' & & `ny_evi_fem_m' & `ny_not_fem_m' & `ny_acs_fem_m' \\ 
tex & `chi_evi_fem_se' & `chi_not_fem_se' & `chi_acs_fem_se' & & `ny_evi_fem_se' & `ny_not_fem_se' & `ny_acs_fem_se' \\ 

tex $x ~~Black & `chi_evi_bla_m' & `chi_not_bla_m' & `chi_acs_bla_m' & & `ny_evi_bla_m' & `ny_not_bla_m' & `ny_acs_bla_m' \\ 
tex & `chi_evi_bla_se' & `chi_not_bla_se' & `chi_acs_bla_se' & & `ny_evi_bla_se' & `ny_not_bla_se' & `ny_acs_bla_se' \\

tex $x ~~Hispanic & `chi_evi_hisp_m' & `chi_not_hisp_m' & `chi_acs_hisp_m' & & `ny_evi_hisp_m' & `ny_not_hisp_m' & `ny_acs_hisp_m' \\ 
tex & `chi_evi_hisp_se' & `chi_not_hisp_se' & `chi_acs_hisp_se' & & `ny_evi_hisp_se' & `ny_not_hisp_se' & `ny_acs_hisp_se' \\

tex $x ~~Quarterly earnings & `chi_evi_earn_m' & `chi_not_earn_m' & `chi_acs_earn_m' & & `ny_evi_earn_m' & `ny_not_earn_m' & `ny_acs_earn_m' \\ 
tex & `chi_evi_earn_se' & `chi_not_earn_se' & `chi_acs_earn_se' & & `ny_evi_earn_se' & `ny_not_earn_se' & `ny_acs_earn_se' \\

tex $x ~~Employment & `chi_evi_prop_m' & `chi_not_prop_m' & `chi_acs_prop_m' & & `ny_evi_prop_m' & `ny_not_prop_m' & `ny_acs_prop_m' \\ 
tex & `chi_evi_prop_se' & `chi_not_prop_se' & `chi_acs_prop_se' & & `ny_evi_prop_se' & `ny_not_prop_se' & `ny_acs_prop_se' \\

tex $x ~~Neighborhood poverty rate (5 yr avg) & `chi_evi_povr_m' & `chi_not_povr_m' & `chi_acs_povr_m' & & `ny_evi_povr_m' & `ny_not_povr_m' & `ny_acs_povr_m' \\ 
tex & `chi_evi_povr_se' & `chi_not_povr_se' & `chi_acs_povr_se' & & `ny_evi_povr_se' & `ny_not_povr_se' & `ny_acs_povr_se' \\

tex $x ~~Neighborhood median rent (5 yr avg) & `chi_evi_rent_m' & `chi_not_rent_m' & `chi_acs_rent_m' & & `ny_evi_rent_m' & `ny_not_rent_m' & `ny_acs_rent_m' \\ 
tex & `chi_evi_rent_se' & `chi_not_rent_se' & `chi_acs_rent_se' & & `ny_evi_rent_se' & `ny_not_rent_se' & `ny_acs_rent_se' \\

tex $x \textbf{\underline{Case characteristics:}} & & & & & & & \\

tex $x ~~Ad damnum (1000s) & `chi_evi_dam_m' & `chi_not_dam_m' & `chi_acs_dam_m' & & `ny_evi_dam_m' & `ny_not_dam_m' & `ny_acs_dam_m' \\ 
tex & `chi_evi_dam_se' & `chi_not_dam_se' & `chi_acs_dam_se' & & `ny_evi_dam_se' & `ny_not_dam_se' & `ny_acs_dam_se' \\

tex $x ~~No prior case & `chi_evi_prio_m' & `chi_not_prio_m' & `chi_acs_prio_m' & & `ny_evi_prio_m' & `ny_not_prio_m' & `ny_acs_prio_m' \\ 
tex & `chi_evi_prio_se' & `chi_not_prio_se' & `chi_acs_prio_se' & & `ny_evi_prio_se' & `ny_not_prio_se' & `ny_acs_prio_se' \\

tex $x ~~Tenant without attorney & `chi_evi_att_m' & `chi_not_att_m' & `chi_acs_att_m' & & `ny_evi_att_m' & `ny_not_att_m' & `ny_acs_att_m' \\ 
tex & `chi_evi_att_se' & `chi_not_att_se' & `chi_acs_att_se' & & `ny_evi_att_se' & `ny_not_att_se' & `ny_acs_att_se' \\

tex \midrule
tex $x Observations & `chi_evi_obs_m' & `chi_not_obs_m' & `chi_acs_obs_m' & & `ny_evi_obs_m' & `ny_not_obs_m' & `ny_acs_obs_m' \\

tex \bottomrule \\

tex	\end{tabular} }
texdoc close	
	
